16 | “order by”是怎么工作的？

假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄。
假设这个表的部分定义是这样的：

1
2
3
4
5
6
7
8
9
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

这时，你的 SQL 语句可以这么写：

1
select city,name,age from t where city='杭州' order by name limit 1000  ;

全字段排序

为避免全表扫描，我们需要在 city 字段加上索引。
在 city 字段上创建索引之后，我们用 explain 命令来看看这个语句的执行情况。
image.png
Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。【sort buffer是在server层】
通常情况下，这个语句执行流程如下所示 ：

  1. 初始化 sort_buffer，确定放入 name、city、age 这三个字段；【放入sort_buffer的字段是select字段确定的】
  2. 从索引 city 找到第一个满足 city=’杭州’条件的主键 id，也就是图中的 ID_X；
  3. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；
  6. 对 sort_buffer 中的数据按照字段 name 做快速排序；
  7. 按照排序结果取前 1000 行返回给客户端。
    image.png
    图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。
    sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
    你可以用下面介绍的方法，来确定一个排序语句是否使用了临时文件。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    /* 打开 optimizer_trace，只对本线程有效 */
    SET optimizer_trace='enabled=on'; 
     
    /* @a 保存 Innodb_rows_read 的初始值 */
    select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';
     
    /* 执行语句 */
    select city, name,age from t where city='杭州' order by name limit 1000; 
     
    /* 查看 OPTIMIZER_TRACE 输出 */
    SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
     
    /* @b 保存 Innodb_rows_read 的当前值 */
    select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
     
    /* 计算 Innodb_rows_read 差值 */
    select @b-@a;
    这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files 中看到是否使用了临时文件。
    image.png

    记得要分析的select 语句和 SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 要一起执行，在不同的结果集查看分析结果，否则会看不到想看的select语句分析结果。

number_of_tmp_files 表示的是，排序过程中使用的临时文件数。你一定奇怪，为什么需要 12 个文件？内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。
如果 sort_buffer_size 超过了需要排序的数据量的大小，number_of_tmp_files 就是 0，表示排序可以直接在内存中完成。
否则就需要放在临时文件中排序。sort_buffer_size 越小，需要分成的份数越多，number_of_tmp_files 的值就越大。
接下来，我再和你解释一下图 4 中其他两个值的意思。

  • examined_rows：examined_rows=4000，表示参与排序的行数是 4000 行(表里有4000 条满足 city=’杭州’的记录)
  • sort_mode：里面的 packed_additional_fields 的意思是，排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

    varchar存储数据，使用1或者2字节（数据长度超过255时使用2字节）保存数据长度，保存格式为：长度+数据。这里存放“yes”应该只要4个字节的空间。https://dev.mysql.com/doc/refman/8.0/en/char.html

最后一个查询语句 select @b-@a 的返回结果是 4000，表示整个执行过程只扫描了 4000 行

  • 这里需要注意的是，为了避免对结论造成干扰，我把 internal_tmp_disk_storage_engine 设置成 MyISAM。否则，select @b-@a 的结果会显示为 4001。
  • 这是因为查询 OPTIMIZER_TRACE 这个表时，需要用到临时表，而 internal_tmp_disk_storage_engine 的默认值是 InnoDB。如果使用的是 InnoDB 引擎的话，把数据从临时表取出来的时候，会让 Innodb_rows_read 的值加 1。

rowid排序

如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。
所以如果单行很大，这个方法效率不够好。
如果 MySQL 认为排序的单行长度太大会怎么做呢？
接下来，我来修改一个参数，让 MySQL 采用另外一种算法

1
SET max_length_for_sort_data = 16;

max_length_for_sort_data：是 MySQL 中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。
city、name、age 这三个字段的定义总长度是 36，我把 max_length_for_sort_data 设置为 16，我们再来看看计算过程有什么改变。
新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id。
但这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

  1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
  2. 从索引 city 找到第一个满足 city=’杭州’条件的主键 id，也就是图中的 ID_X；
  3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到不满足 city=’杭州’条件为止，也就是图中的 ID_Y；
  6. 对 sort_buffer 中的数据按照字段 name 进行排序；
  7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。
    image.png
    对比全字段排序流程图你会发现，rowid 排序多访问了一次表 t 的主键索引，就是步骤7
    需要说明的是，最后的“结果集”是一个逻辑概念，实际上 MySQL 服务端从排序后的 sort_buffer 中依次取出 id，然后到原表查到 city、name 和 age 这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

    [!note]
    rowid 方式和全字段方式一样，需要先把查询到的结果全部放在内存或硬盘中，再使用相关算法进行排序。而排序后由于没有保存所需的字段，需要按顺序使用主键再从索引树上查询，查到一个就返回一个，而不用把所有内容查完放到内存上再一并返回。

这个时候执行 select @b-@a，结果会是多少呢？现在，我们就来看看结果有什么不同。
image.png

  • 首先，图中的 examined_rows 的值还是 4000，表示用于排序的数据是 4000 行。但是 select @b-@a 这个语句的值变成 5000 了。
    • 因为这时候除了排序过程外，在排序完成后，还要根据 id 去原表取值。由于语句是 limit 1000，因此会多读 1000 行。
  • sort_mode 变成了 <sort_key, rowid>，表示参与排序的只有 name 和 id 这两个字段。
  • number_of_tmp_files 变成 10 了，是因为这时候参与排序的行数虽然仍然是 4000 行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。

全字段排序 VS rowid 排序

我们来分析一下，从这两个执行流程里，还能得出什么结论。
如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。
如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
这也就体现了 MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。
对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。
其实，并不是所有的 order by 语句，都需要排序操作的。从上面分析的执行过程，我们可以看到，MySQL 之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

你可以设想下，如果能够保证从 city 这个索引上取出来的行，天然就是按照 name 递增排序的话，是不是就可以不用再排序了呢？
我们可以在这个市民表上创建一个 city 和 name 的联合索引

1
alter table t add index city_user(city, name);

作为与 city 索引的对比，我们来看看这个索引的示意图。
image.png
在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足 city=’杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要 city 的值是杭州，name 的值就一定是有序的。
这样整个查询过程的流程就变成了：

  1. 从索引 (city,name) 找到第一个满足 city=’杭州’条件的主键 id；
  2. 到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
  3. 从索引 (city,name) 取下一个记录主键 id；
  4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束。

可以看到，这个查询过程不需要临时表，也不需要排序。接下来，我们用 explain 的结果来印证一下。
image.png
从图中可以看到，Extra 字段中没有 Using filesort 了，也就是不需要排序了。而且由于 (city,name) 这个联合索引本身有序，所以这个查询也不用把 4000 行全都读一遍，只要找到满足条件的前 1000 条记录就可以退出了。也就是说，在我们这个例子里，只需要扫描 1000 次。
这里我们可以再稍微复习一下。覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。
按照覆盖索引的概念，我们可以再优化一下这个查询语句的执行流程。
针对这个查询，我们可以创建一个 city、name 和 age 的联合索引，对应的 SQL 语句就是：

1
alter table t add index city_user_age(city, name, age);

这时，对于 city 字段的值相同的行来说，还是按照 name 字段的值递增排序的，此时的查询语句也就不再需要排序了。这样整个查询语句的执行流程就变成了：

  1. 从索引 (city,name,age) 找到第一个满足 city=’杭州’条件的记录，取出其中的 city、name 和 age 这三个字段的值，作为结果集的一部分直接返回；
  2. 从索引 (city,name,age) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
  3. 重复执行步骤 2，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束。

image.png
然后，我们再来看看 explain 的结果。
image.png
可以看到，Extra 字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。
当然，这里并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。

索引并不是越多越好！索引可以提高效率同样可以降低效率。 索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。 因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。 限制每张表上的索引数量,建议单张表索引不超过 5 个

问题

select * from t where city in (“杭州”,” 苏州 “) order by name limit 100; 这个 SQL 语句是否需要排序？有什么方案可以避免排序？
同时查了”杭州”和” 苏州 “两个城市，因此所有满足条件的 name 就不是递增的了。也就是说，这条 SQL 语句需要排序。
为了避免排序，这里，我们要用到 (city,name) 联合索引的特性，把这一条语句拆成两条语句，执行流程如下：

  1. 执行 select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的，客户端用一个长度为 100 的内存数组 A 保存结果。
  2. 执行 select * from t where city=“苏州” order by name limit 100; 用相同的方法，假设结果被存进了内存数组 B。
  3. 现在 A 和 B 是两个有序数组，然后你可以用归并排序的思想，得到 name 最小的前 100 值，就是我们需要的结果了。

如果把这条 SQL 语句里“limit 100”改成“limit 10000,100”的话，处理方式其实也差不多，即：要把上面的两条语句改成写：

1
select * from t where city=" 杭州 " order by name limit 10100;

和

1
select * from t where city=" 苏州 " order by name limit 10100。

这时候数据量较大，可以同时起两个连接一行行读结果，用归并排序算法拿到这两个结果集里，按顺序取第 10001~10100 的 name 值，就是需要的结果了。
当然这个方案有一个明显的损失，就是从数据库返回给客户端的数据量变大了。
所以，如果数据的单行比较大的话，可以考虑把这两条 SQL 语句改成下面这种写法：

1
select id,name from t where city=" 杭州 " order by name limit 10100;

和

1
select id,name from t where city="苏州" order by name limit 10100。

然后，再用归并排序的方法取得按 name 顺序第 10001~10100 的 name、id 的值，然后拿着这 100 个 id 到数据库中去查出所有记录。
上面这些方法，需要你根据性能需求和开发的复杂度做出权衡。

其他问题

[!question]
老师，基于早上知道的sort_buffer是在server层，我重新理解了下rowid排序的过程，
1，执行器查看表定义，发现name、city、age字段的长度之和超过max_length_for_sort_data，所以初始化sort_buffer的时候只放入id和name字段。
2，执行器调用存储引擎的读数据接口，依次获取满足条件的数据的id和name，存入sort_buffer。
3，排序。
4，执行器根据limit条件筛选出id，再次调用引擎读数据的接口获取相应的数据，返回客户端。
整个过程实际上是被执行器拆成了两次查询，共调用两次存储层的读数据接口，所以总的扫描行数需要相加。（@b-@a=5000）
但是对于using index condition的场景，执行器只调用了一次查询接口，回表是由存储层来完成的，所以扫描行数只算一次，即只算走索引搜索的过程中扫描的行数。（@b-@a只会是4000）
不知道这么理解对不对？

不仅对，而且非常好！👍👍
把两个知识点连起来了。是的：

  1. rows_examined就是“server层调用引擎取一行的时候”加1；
  2. 引擎内部自己调用，读取行，不加1；

再补充一个例子：
加索引的时候，也要扫描全表，但如果是inplace DDL（@第13篇），你会看到扫描行数是0，也是因为这些扫描动作都是引擎内部自己调用的。

关于使用varchar
1个字节8位，最大值是 255，用来记录 varchar 的长度。如果大于255，1个字节无法存储，因此需要2个字节（16位）

[!question]
关于查询时使用create_time排序是否会走索引

1)无条件查询如果只有order by create_time,即便create_time上有索引,也不会使用到。
因为优化器认为走二级索引再去回表成本比全表扫描排序更高。
所以选择走全表扫描,然后根据老师讲的两种方式选择一种来排序
2)无条件查询但是是 order by create_time limit m. 如果m值较小,是可以走索引的.
因为优化器认为根据索引有序性去回表查数据,然后得到m条数据,就可以终止循环,那么成本比全表扫描小,则选择走二级索引。
即便没有二级索引,mysql针对order by limit也做了优化,采用堆排序。

[!question]
问题一，在跟max_length_for_sort_data坐比较时，mysql是怎么判断一行数据的大小的？是直接根据表定义字段的大小吗？
问题二，另外这‘一行’的含义是整行数据，还是单单最终引擎层需要返回的字段(即select字段+where字段+order by字段)？

  1. 需要的字段的定义大小的和

  2. 好问题。首先取决于使用的算法。

    a) 如果是全字段排序就是select字段+where字段+order by字段,
    b) 如果是row_id排序，就是order by字段+row_id

[!abstract]
总结一下本节的内容

1.MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size
1>如果排序的数据量小于sort_buffer_size，排序将会在内存中完成
2>如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
3>在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件
2.mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序
1>如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
2>该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表
3>按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表
3.按照排序的结果返回客户所取行数

全字段排序
1.通过索引将所需的字段全部读取到sort_buffer中
2.按照排序字段进行排序
3.将结果集返回给客户端
缺点：
1.造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差
优点：MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作

rowid排序
1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
2.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
3.按照排序后的顺序，取id进行回表取出想要获取的数据
4.将结果集返回给客户端
优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问
缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问

17 | 如何正确地显示随机消息？

根据每个用户的级别有一个单词表，然后这个用户每次访问首页的时候，都会随机滚动显示三个单词。【这个需求有些类似与给用户推荐新闻，但是关键点有两个： 1、如何高效的保证随机性。 2、如何保证随机性的同时确保推荐的不会和之前推荐的重复。】
为了便于理解，我对这个例子进行了简化：去掉每个级别的用户都有一个对应的单词表这个逻辑，直接就是从一个单词表中随机选出三个单词。这个表的建表语句和初始数据的命令如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;
 
call idata();

内存临时表

首先，你会想到用 order by rand() 来实现这个逻辑。

1
mysql> select word from words order by rand() limit 3;

我们先用 explain 命令来看看这个语句的执行情况。
image.png
Extra 字段显示 Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。
因此这个 Extra 的意思就是，需要临时表，并且需要在临时表上排序。

小总结
什么情况下需要使用到临时表呢？ 临时表有内存和硬盘之分， tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。 磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。

你觉得对于临时内存表的排序来说，它会选择哪一种算法呢？回顾一下上一篇文章的一个结论：对于 InnoDB 表来说，执行全字段排序会减少磁盘访问，因此会被优先选择。
我强调了“InnoDB 表”，你肯定想到了，对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。优化器没有了这一层顾虑，那么它会优先考虑的，就是用于排序的行越小越好了，所以，MySQL 这时就会选择 rowid 排序。【临时表在内存中，因此回表过程不会访问磁盘】

关于临时表，自己搜了一些资料，如下：
资料：当查询语句无法直接利用现有的索引进行优化时，会使用到临时表。这些文件通常存储在临时文件夹中，文件名以 #sql 开头，后面跟着一个随机字符串。这些临时缓存文件在查询结束后会被删除。
MySQL 的排序操作可以分为两种：使用索引排序和不使用索引排序。
使用索引排序时，MySQL 会利用索引中已经按照排序顺序排好的数据直接返回给客户端，而不需要使用临时表进行排序。
不使用索引排序时，MySQL 会使用 filesort 算法进行排序。在使用 filesort 算法进行排序时，MySQL 会创建一个临时表，并将需要排序的数据全部存储到该临时表中，然后对该临时表中的数据进行排序，最后返回排序结果给客户端。（主要是一些复杂排序使用临时表）

理解了这个算法选择的逻辑，我们再来看看语句的执行流程。同时，通过今天的这个例子，我们来尝试分析一下语句的扫描行数。
这条语句的执行流程是这样的：

  1. 创建一个临时表。这个临时表使用的是 memory 引擎，表里有两个字段，第一个字段是 double 类型，为了后面描述方便，记为字段 R，第二个字段是 varchar(64) 类型，记为字段 W。并且，这个表没有建索引。
  2. 从 words 表中，按主键顺序取出所有的 word 值。对于每一个 word 值，调用 rand() 函数生成一个大于 0 小于 1 的随机小数，并把这个随机小数和 word 分别存入临时表的 R 和 W 字段中，到此，扫描行数是 10000。
  3. 现在临时表有 10000 行数据了，接下来你要在这个没有索引的内存临时表上，按照字段 R 排序。
  4. 初始化 sort_buffer。sort_buffer 中有两个字段，一个是 double 类型，另一个是整型。
  5. 从内存临时表中一行一行地取出 R 值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入 sort_buffer 中的两个字段里。这个过程要对内存临时表做全表扫描，此时扫描行数增加 10000，变成了 20000。
  6. 在 sort_buffer 中根据 R 的值进行排序。注意，这个过程没有涉及到表操作，所以不会增加扫描行数。
  7. 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出 word 值，返回给客户端。这个过程中，访问了表的三行数据，总扫描行数变成了 20003。

接下来，我们通过慢查询日志（slow log）来验证一下我们分析得到的扫描行数是否正确。

1
# Query_time: 0.900376  Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003SET timestamp=1541402277;select word from words order by rand() limit 3;

其中，Rows_examined：20003 就表示这个语句执行过程中扫描了 20003 行，也就验证了我们分析得出的结论。
这里插一句题外话，在平时学习概念的过程中，你可以经常这样做，先通过原理分析算出扫描行数，然后再通过查看慢查询日志，来验证自己的结论。我自己就是经常这么做，这个过程很有趣，分析对了开心，分析错了但是弄清楚了也很开心。
现在，我来把完整的排序执行流程图画出来。
image.png
图中的 pos 就是位置信息，你可能会觉得奇怪，这里的“位置信息”是个什么概念？在上一篇文章中，我们对 InnoDB 表排序的时候，明明用的还是 ID 字段。
这时候，我们就要回到一个基本概念：MySQL 的表是用什么方法来定位“一行数据”的。
有几位同学问到，如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法回表了？
其实不是的。如果你创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。
这也就是排序模式里面，rowid 名字的来历。实际上它表示的是：每个引擎用来唯一标识数据行的信息。

  • 对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；
  • 对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；
  • MEMORY 引擎不是索引组织表。在这个例子里面，你可以认为它就是一个数组。因此，这个 rowid 其实就是数组的下标。

到这里，我来稍微小结一下：order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。

磁盘临时表

并不是所有的临时表都是内存表！
tmp_table_size 这个配置限制了内存临时表的大小，默认值是 16M。如果临时表大小超过了 tmp_table_size，那么内存临时表就会转成磁盘临时表。
磁盘临时表使用的引擎默认是 InnoDB，是由参数 internal_tmp_disk_storage_engine 控制的。
当使用磁盘临时表的时候，对应的就是一个没有显式索引的 InnoDB 表的排序过程。
为了复现这个过程，我把 tmp_table_size 设置成 1024，把 sort_buffer_size 设置成 32768, 把 max_length_for_sort_data 设置成 16。

  1. tmp_table_size 设置成 1K，设置内存临时表的大小为1K；
  2. sort_buffer_size 设置成32K，排序开辟的内存大小设置成32K；
  3. max_length_for_sort_data ：用于排序的行数据的长度，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。 将 max_length_for_sort_data 设置成 16，小于 word 字段的长度定义，因此使用rowid 排序
1
2
3
4
5
6
7
8
9
10
11
set tmp_table_size=1024;
set sort_buffer_size=32768;
set max_length_for_sort_data=16;
/* 打开 optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on'; 

/* 执行语句 */
select word from words order by rand() limit 3;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

image.png
然后，我们来看一下这次 OPTIMIZER_TRACE 的结果。

OPTIMIZER_TRACE是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。

因为将 max_length_for_sort_data 设置成 16，小于 word 字段的长度定义，所以我们看到 sort_mode 里面显示的是 rowid 排序，这个是符合预期的，参与排序的是随机值 R 字段和 rowid 字段组成的行。
这时候你可能心算了一下，发现不对。R 字段存放的随机值就 8 个字节，rowid 是 6 个字节（至于为什么是 6 字节，就留给你课后思考吧），数据总行数是 10000，这样算出来就有 140000 字节，超过了 sort_buffer_size 定义的 32768 字节了。但是，number_of_tmp_files 的值居然是 0，难道不需要用临时文件吗？
这个 SQL 语句的排序确实没有用到临时文件，采用是 MySQL 5.6 版本引入的一个新的排序算法，即：优先队列排序算法。接下来，我们就看看为什么没有使用临时文件的算法，也就是归并排序算法，而是采用了优先队列排序算法。
如果使用归并排序算法的话，虽然最终也能得到前 3 个值，但是这个算法结束后，已经将 10000 行数据都排好序了。但我们的查询不需要全部有序，浪费资源。而优先队列算法，就可以精确地只得到三个最小值，执行流程如下：

  1. 对于这 10000 个准备排序的 (R,rowid)，先取前三行，构造成一个堆；

（对数据结构印象模糊的同学，可以先设想成这是一个由三个元素组成的数组）
2. 取下一个行 (R’,rowid’)，跟当前堆里面最大的 R 比较，如果 R’小于 R，把这个 (R,rowid) 从堆中去掉，换成 (R’,rowid’)；
3. 重复第 2 步，直到第 10000 个 (R’,rowid’) 完成比较。

image.png
上图是模拟 6 个 (R,rowid) 行，通过优先队列排序找到最小的三个 R 值的行的过程。整个排序过程中，为了最快地拿到当前堆的最大值，总是保持最大值在堆顶，因此这是一个最大堆。
图中的OPTIMIZER_TRACE 结果中，filesort_priority_queue_optimization 这个部分的 chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的 number_of_tmp_files 是 0。
这个流程结束后，我们构造的堆里面，就是这个 10000 行里面 R 值最小的三行。然后，依次把它们的 rowid 取出来，去临时表里面拿到 word 字段，这个过程就跟上一篇文章的 rowid 排序的过程一样了。

我们再看一下上面一篇文章的 SQL 查询语句：

1
select city,name,age from t where city='杭州' order by name limit 1000  ;

你可能会问，这里也用到了 limit，为什么没用优先队列排序算法呢？原因是，这条 SQL 语句是 limit 1000，如果使用优先队列算法的话，需要维护的堆的大小就是 1000 行的 (name,rowid)，超过了我设置的 sort_buffer_size 大小，所以只能使用归并排序算法。

影响是否使用优先队列排序（堆排序的）算法的原因是，sort buffer排序内存的大小，因为这个堆就是占用的sort buffer（排序内存）的。【小于buffer_size就直接快排了】

总之，不论是使用哪种类型的临时表，order by rand() 这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。

随机排序算法

我们先把问题简化一下，如果只随机选择 1 个 word 值，可以怎么做呢？思路上是这样的：

  1. 取得这个表的主键 id 的最大值 M 和最小值 N;
  2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;
  3. 取不小于 X 的第一个 ID 的行。

我们把这个算法，暂时称作随机算法 1。这里，我直接给你贴一下执行语句的序列:

1
mysql> select max(id),min(id) into @M,@N from t ;set @X= floor((@M-@N+1)*rand() + @N);select * from t where id >= @X limit 1;

这个方法效率很高，因为取 max(id) 和 min(id) 都是不需要扫描索引的，而第三步的 select 也可以用索引快速定位，可以认为就只扫描了 3 行。但实际上，这个算法本身并不严格满足题目的随机要求，因为 ID 中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。
比如你有 4 个 id，分别是 1、2、4、5，如果按照上面的方法，那么取到 id=4 的这一行的概率是取得其他行概率的两倍。
如果这四行的 id 分别是 1、2、40000、40001 呢？这个算法基本就能当 bug 来看待了。

为了得到严格随机的结果，你可以用下面这个流程：

  1. 取得整个表的行数，并记为 C。
  2. 取得 Y = floor(C * rand())。 floor 函数在这里的作用，就是取整数部分。
  3. 再用 limit Y,1 取得一行。

我们把这个算法，称为随机算法 2。下面这段代码，就是上面流程的执行语句的序列。

1
2
3
4
5
6
mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

由于 limit 后面的参数不能直接跟变量，所以我在上面的代码中使用了 prepare+execute 的方法。你也可以把拼接 SQL 语句的方法写在应用程序中，会更简单些。
这个随机算法 2，解决了算法 1 里面明显的概率不均匀问题。
MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行，执行代价比随机算法 1 的代价要高。
当然，随机算法 2 跟直接 order by rand() 比起来，执行代价还是小很多的。

现在，我们再看看，如果我们按照随机算法 2 的思路，要随机取 3 个 word 值呢？你可以这么做：取得整个表的行数，记为 C；根据相同的随机方法得到 Y1、Y2、Y3；再执行三个 limit Y, 1 语句得到三行数据。我们把这个算法，称作随机算法 3。下面这段代码，就是上面流程的执行语句的序列。【注意这里是有小概率碰撞的，所以考虑业务是否要进行去重】

1
2
3
4
5
6
7
mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
select * from t limit @Y2，1；
select * from t limit @Y3，1；

今天的例子里面，我们不是仅仅在数据库内部解决问题，还会让应用代码配合拼接 SQL 语句。在实际应用的过程中，比较规范的用法就是：尽量将业务逻辑写在业务代码中，让数据库只做“读写数据”的事情。因此，这类方法的应用还是比较广泛的。

课后问题

问题：通过三次 limit Y,1 来得到需要的数据，你觉得有没有进一步的优化方法？
回答：
这里我给出一种方法，取 Y1、Y2 和 Y3 里面最大的一个数，记为 M，最小的一个数记为 N，然后执行下面这条 SQL 语句：

1
mysql> select * from t limit N, M-N+1;

再加上取整个表总行数的 C 行，这个方案的扫描行数总共只需要 C+M+1 行。
当然也可以先取回 id 值，在应用中确定了三个 id 值以后，再执行三次 where id=X 的语句也是可以的。

对于有空洞，可以在上线前整理数据，把空洞处理掉
可以增加一个主键字段,用来记录每行记录的rowid，这样一万行，那就是连续的一万，然后随机，用该随机rowid回表查询该行记录【本质都是增加无空洞的子增值】

[!note]
该问题还有一种优化方法：【是一个“如何优化大表分页查询”的答法之一】
假设Y1，Y2，Y3是由小到大的三个数，则可以优化成这样，这样扫描行数为Y3
id1 = select * from t limit @Y1，1；
id2= select * from t where id > id1 limit @Y2-@Y1，1；
select * from t where id > id2 limit @Y3 - @Y2，1；

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

案例一：条件字段函数操作

假设你现在维护了一个交易系统，其中交易记录表 tradelog 包含交易流水号（tradeid）、交易员 id（operator）、交易时间（t_modified）等字段。为了便于描述，我们先忽略其他字段。这个表的建表语句如下：

1
2
3
4
5
6
7
8
9
mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设，现在已经记录了从 2016 年初到 2018 年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中 7 月份的交易记录总数。这个逻辑看上去并不复杂，你的 SQL 语句可能会这么写：

1
mysql> select count(*) from tradelog where month(t_modified)=7;

虽然t_modified 字段上有索引，但会执行特别久
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

需要注意的是，优化器并不是要放弃使用这个索引。
在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。因此最终还是会选择索引 t_modified。
接下来，我们使用 explain 命令，查看一下这条 SQL 语句的执行结果。
image.png
key=”t_modified”表示的是，使用了 t_modified 这个索引；我在测试表数据中插入了 10 万行数据，rows=100335，说明这条语句扫描了整个索引的所有值；Extra 字段的 Using index，表示的是使用了覆盖索引。

为了能够用上索引的快速定位能力，我们就要把 SQL 语句改成基于字段本身的范围查询。按照下面这个写法，优化器就能按照我们预期的，用上 t_modified 索引的快速定位能力了。

where 后面有or不是会导致索引部分失效吗？不会，只要是同个字段就不会失效

1
2
3
4
mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

当然，如果你的系统上线时间更早，或者后面又插入了之后年份的数据的话，你就需要再把其他年份补齐。

不过优化器在个问题上确实有“偷懒”行为，即使是对于不改变有序性的函数，也不会考虑使用索引。比如，对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以，需要你在写 SQL 语句的时候，手动改写成 where id = 10000 -1 才可以。

案例二：隐式类型转换

1
mysql> select * from tradelog where tradeid=110717;

交易编号 tradeid 这个字段上，本来就有索引，但是 explain 的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。
那么，现在这里就有两个问题：
1、 数据类型转换的规则是什么？
2、 为什么有数据类型转换，就需要走全索引扫描？
这里有一个简单的方法，看 select “10” > 9 的结果：

  • 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是 1；
  • 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是 0。

在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。
对于优化器来说，上面的这个语句相当于：

1
mysql> select * from tradelog where  CAST(tradid AS signed int) = 110717;

案例三：隐式字符编码转换

假设系统里还有另外一个表 trade_detail，用于记录交易的操作细节。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> CREATE TABLE `trade_detail` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `trade_step` int(11) DEFAULT NULL, /*操作步骤*/
  `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tradelog values(1, 'aaaaaaaa', 1000, now());
insert into tradelog values(2, 'aaaaaaab', 1000, now());
insert into tradelog values(3, 'aaaaaaac', 1000, now());

insert into trade_detail values(1, 'aaaaaaaa', 1, 'add');
insert into trade_detail values(2, 'aaaaaaaa', 2, 'update');
insert into trade_detail values(3, 'aaaaaaaa', 3, 'commit');
insert into trade_detail values(4, 'aaaaaaab', 1, 'add');
insert into trade_detail values(5, 'aaaaaaab', 2, 'update');
insert into trade_detail values(6, 'aaaaaaab', 3, 'update again');
insert into trade_detail values(7, 'aaaaaaab', 4, 'commit');
insert into trade_detail values(8, 'aaaaaaac', 1, 'add');
insert into trade_detail values(9, 'aaaaaaac', 2, 'update');
insert into trade_detail values(10, 'aaaaaaac', 3, 'update again');
insert into trade_detail values(11, 'aaaaaaac', 4, 'commit');

这时候，如果要查询 id=2 的交易的所有操作步骤信息，SQL 语句可以这么写：

1
mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /* 语句 Q1*/

image.png
我们一起来看下这个结果：

  1. 第一行显示优化器会先在交易记录表 tradelog 上查到 id=2 的行，这个步骤用上了主键索引，rows=1 表示只扫描一行；
  2. 第二行 key=NULL，表示没有用上交易详情表 trade_detail 上的 tradeid 索引，进行了全表扫描。

在这个执行计划里，是从 tradelog 表中取 tradeid 字段，再去 trade_detail 表里查询匹配字段。因此，我们把 tradelog 称为驱动表，把 trade_detail 称为被驱动表，把 tradeid 称为关联字段。
接下来，我们看下这个 explain 结果表示的执行流程：
image.png

  • 第 1 步，是根据 id 在 tradelog 表里找到 L2 这一行；
  • 第 2 步，是从 L2 中取出 tradeid 字段的值；
  • 第 3 步，是根据 tradeid 值到 trade_detail 表中查找条件匹配的行。explain 的结果里面第二行的 key=NULL 表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断 tradeid 的值是否匹配。

进行到这里，你会发现第 3 步不符合我们的预期。因为表 trade_detail 里 tradeid 字段上是有索引的，我们本来是希望通过使用 tradeid 索引能够快速定位到等值的行。但，这里并没有。
因为这两个表的字符集不同，一个是 utf8，一个是 utf8mb4，所以做表连接查询的时候用不上关联字段的索引。但为什么字符集不同就用不上索引呢？
字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。

这个设定很好理解，utf8mb4 是 utf8 的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

因此， 在执行上面这个语句的时候，需要将被驱动数据表里的字段一个个地转换成 utf8mb4，再跟 L2 做比较。
也就是说，实际上这个语句等同于下面这个写法：

1
select * from trade_detail  where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

CONVERT() 函数，在这里的意思是把输入的字符串转成 utf8mb4 字符集。
这就再次触发了我们上面说到的原则：对索引字段做函数操作，优化器会放弃走树搜索功能。
到这里，你终于明确了，字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。
作为对比验证，我给你提另外一个需求，“查找 trade_detail 表里 id=4 的操作，对应的操作者是谁”，再来看下这个语句和它的执行计划。

1
mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;

image.png
这个语句里 trade_detail 表成了驱动表，但是 explain 结果的第二行显示，这次的查询操作用上了被驱动表 tradelog 里的索引 (tradeid)，扫描行数是 1。

这也是两个 tradeid 字段的 join 操作，为什么这次能用上被驱动表的 tradeid 索引呢？我们来分析一下。
假设驱动表 trade_detail 里 id=4 的行记为 R4，那么在连接的时候（图 5 的第 3 步），被驱动表 tradelog 上执行的就是类似这样的 SQL 语句：

1
select operator from tradelog  where traideid =$R4.tradeid.value;

这时候 $R4.tradeid.value 的字符集是 utf8, 按照字符集转换规则，要转成 utf8mb4，所以这个过程就被改写成：

1
select operator from tradelog  where traideid =CONVERT($R4.tradeid.value USING utf8mb4);

你看，这里的 CONVERT 函数是加在输入参数上的，这样就可以用上被驱动表的 traideid 索引。
理解了原理以后，就可以用来指导操作了。如果要优化语句

1
select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

的执行过程，有两种做法：

  • 比较常见的优化方法是，把 trade_detail 表上的 tradeid 字段的字符集也改成 utf8mb4，这样就没有字符集转换的问题了。
1
alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;
  • 如果能够修改字段的字符集的话，是最好不过了。但如果数据量比较大， 或者业务上暂时不能做这个 DDL 的话，那就只能采用修改 SQL 语句的方法了。
1
mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

image.png
这里，我主动把 l.tradeid 转成 utf8，就避免了被驱动表上的字符编码转换，从 explain 结果可以看到，这次索引走对了【当然文中不存在丢失精度的问题，毕竟utf8mb4转换成utf8是要考虑出现异常的情况】

问题

希望你可以分享一下之前碰到过的、与文章中类似的场景。@封建的风 提到一个有趣的场景，值得一说。我把他的问题重写一下，表结构如下：

1
2
3
4
5
6
mysql> CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

假设现在表里面，有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’， 假设现在执行语句是这么写的:

1
mysql> select * from table_a where b='1234567890abcd';

这时候，MySQL 会怎么执行呢？
最理想的情况是，MySQL 看到字段 b 定义的是 varchar(10)，那肯定返回空呀。可惜，MySQL 并没有这么做。那要不，就是把’1234567890abcd’拿到索引里面去做匹配，肯定也没能够快速判断出索引树 b 上并没有这个值，也很快就能返回空结果。
但实际上，MySQL 也不是这么做的。这条 SQL 语句的执行很慢，流程是这样的

  1. 在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；
  2. 这样满足条件的数据有 10 万行；
  3. 因为是 select *， 所以要做 10 万次回表；【这里完善下，老师应该是为了说明例子，正常业务不只2个字段，不然应该走覆盖索引不会回表的】
  4. 但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;
  5. 返回结果是空。

这个例子，是我们文章内容的一个很好的补充。虽然执行过程中可能经过函数操作，但是最终在拿到结果后，server 层还是要做一轮判断的。

其他问题

[!question]
老师你好，我在执行explain的时候，发现extra 里面 有using where，using index ，using index condition 能具体讲下这几个的区别吗？

Using index是覆盖索引
Using index condition 是索引下推
using where 说明执行器还会进行过滤；
一般where语句会被拆解成 index key，index filter，table filter
index key：树搜索
index filter: icp
table filter: 执行器过滤

[!question]
sql语句执行时那些order by group by limit 以及where条件，有执行的先后顺序吗？

一般而言，SQL 查询的执行顺序如下：

  1. FROM：首先，执行 FROM 子句，确定要查询的数据源表。这包括表的连接和关联操作。
  2. WHERE：接下来，应用 WHERE 子句中的条件，对数据进行过滤。只有符合条件的行才会进入下一步处理。
  3. GROUP BY：如果查询包含 GROUP BY 子句，那么在聚合操作之前，会将结果集按照 GROUP BY 子句中指定的列进行分组。
  4. HAVING：在 GROUP BY 分组之后，应用 HAVING 子句中的条件进行过滤。只有满足条件的分组才会保留下来。
  5. SELECT：在上述步骤完成后，执行 SELECT 子句，选择要返回的列。
  6. ORDER BY：最后，应用 ORDER BY 子句中的排序规则对结果进行排序。
  7. LIMIT：如果查询包含 LIMIT 子句，那么在所有操作完成之后，应用 LIMIT 子句并返回指定数量的结果行。

[!question]
一个有趣的问题【不满足最左前缀匹配原则时也走了索引】

这个查询语句会对t3做全索引扫描，是使用了索引的，只是没有用上快速搜索功能——>个人理解是使用了覆盖索引，但由于没使用status，所以走全索引扫描即没用快速搜索功能
（优化器可以使用该索引来定位满足 t3.ootime < UNIX_TIMESTAMP('2022-01-18') 条件的行。这种情况下，MySQL 会扫描索引，并且只需要访问索引的数据页，而不需要回表访问表的数据行。）
image.png

19 | 为什么我只查一行的语句，也执行这么慢？

需要说明的是，如果 MySQL 数据库本身就有很大的压力，导致数据库服务器 CPU 占用率很高或 ioutil（IO 利用率）很高，这种情况下所有语句的执行都有可能变慢，不属于我们今天的讨论范围。

为了便于描述，我还是构造一个表，基于这个表来说明今天的问题。这个表有两个字段 id 和 c，并且我在里面插入了 10 万行记录。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
 
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;
 
call idata();

第一类：查询长时间不返回

如图 1 所示，在表 t 执行下面的 SQL 语句：

1
mysql> select * from t where id=1;

查询结果长时间不返回。
一般碰到这种情况的话，大概率是表 t 被锁住了。接下来分析原因的时候，一般都是首先执行一下 show processlist 命令，看看当前语句处于什么状态。
然后我们再针对每种状态，去分析它们产生的原因、如何复现，以及如何处理。

等 MDL 锁

如图所示，就是使用 show processlist 命令查看 Waiting for table metadata lock 的示意图。
image.png
出现这个状态表示的是，现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。
[[基础篇 1-8节#06 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？]]我给你介绍过一种复现方法。但需要说明的是，那个复现过程是基于 MySQL 5.6 版本的。MySQL 5.7 版本修改了 MDL 的加锁策略，所以就不能复现这个场景了。
不过，在 MySQL 5.7 版本下复现这个场景，也很容易。如图 3 所示，我给出了简单的复现步骤。
image.png
session A 通过 lock table 命令持有表 t 的 MDL 写锁，而 session B 的查询需要获取 MDL 读锁。所以，session B 进入等待状态。
这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。【MDL 锁等待时间过长的解决方案】

但是，由于在 show processlist 的结果里面，session A 的 Command 列是“Sleep”，导致查找起来很不方便。不过有了 performance_schema 和 sys 系统库以后，就方便多了。（MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)
通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。
image.png

等 flush

我在表 t 上，执行下面的 SQL 语句：

1
mysql> select * from information_schema.processlist where id=1;

如图，我查出来这个线程的状态是 Waiting for table flush，你可以设想一下这是什么原因。
image.png
这个状态表示的是，现在有一个线程正要对表 t 做 flush 操作。MySQL 里面对表做 flush 操作的用法，一般有以下两个：

1
2
flush tables t with read lock; 
flush tables with read lock;

这两个 flush 语句，如果指定表 t 的话，代表的是只关闭表 t；如果没有指定具体的表名，则表示关闭 MySQL 里所有打开的表。
但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。
所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。
现在，我们一起来复现一下这种情况，复现步骤如下图所示：
image.png
在 session A 中，我故意每行都调用一次 sleep(1)，这样这个语句默认要执行 10 万秒，在这期间表 t 一直是被 session A“打开”着。然后，session B 的 flush tables t 命令再要去关闭表 t，就需要等 session A 的查询结束。这样，session C 要再次查询的话，就会被 flush 命令堵住了。
图 7 是这个复现步骤的 show processlist 结果。这个例子的排查也很简单，你看到这个 show processlist 的结果，肯定就知道应该怎么做了。

尝试回答下解法：
先将slect sleep(1) from t 的进程先结束，【show processlist 的结果里就有id了，前面等MDL锁的时候不能直接用这个ID是因为看不到具体的执行命令(info)，这里能看到应该就可以直接定位到了。】
然后然flush table t的命令执行完，
select * from t where id=1 的命令就能执行了

image.png

等行锁

现在，经过了表级锁的考验，我们的 select 语句终于来到引擎里了。

1
mysql> select * from t where id=1 lock in share mode;

备注：该语句在[[基础篇 1-8节#08 事务到底是隔离的还是不隔离的？]]介绍过
由于访问 id=1 这个记录时要加读锁，如果这时候已经有一个事务在这行记录上持有一个写锁，我们的 select 语句就会被堵住。
复现步骤和现场如下：
image.png
image.png
显然，session A 启动了事务，占有写锁，还不提交，是导致 session B 被堵住的原因。
这个问题并不难分析，但问题是怎么查出是谁占着这个写锁。如果你用的是 MySQL 5.7 版本，可以通过 sys.innodb_lock_waits 表查到。
查询方法是：

1
mysql> select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'`\G

image.png
可以看到，这个信息很全，4 号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式，就是 KILL QUERY 4 或 KILL 4。
不过，这里不应该显示“KILL QUERY 4”。这个命令表示停止 4 号线程当前正在执行的语句，而这个方法其实是没有用的。因为占有行锁的是 update 语句，这个语句已经是之前执行完成了的，现在执行 KILL QUERY，无法让这个事务去掉 id=1 上的行锁。
实际上，KILL 4 才有效，也就是说直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 上的行锁。

第二类：查询慢

先来看一条你一定知道原因的 SQL 语句：

1
mysql> select * from t where c=50000 limit 1;

由于字段 c 上没有索引，这个语句只能走 id 主键顺序扫描，因此需要扫描 5 万行。
作为确认，你可以看一下慢查询日志。注意，这里为了把所有语句记录到 slow log 里，我在连接后先执行了 set long_query_time=0，将慢查询日志的时间阈值设置为 0。
image.png
Rows_examined 显示扫描了 50000 行。你可能会说，不是很慢呀，11.5 毫秒就返回了，我们线上一般都配置超过 1 秒才算慢查询。但你要记住：坏查询不一定是慢查询。我们这个例子里面只有 10 万行记录，数据量大起来的话，执行时间就线性涨上去了。
扫描行数多，所以执行慢，这个很好理解。

但是接下来，我们再看一个只扫描一行，但是执行很慢的语句。
如下图，是这个例子的 slow log。可以看到，执行的语句是

1
mysql> select * from t where id=1；

虽然扫描行数是 1，但执行时间却长达 800 毫秒。
image.png
是不是有点奇怪呢，这些时间都花在哪里了？
如果我把这个 slow log 的截图再往下拉一点，你可以看到下一个语句，select * from t where id=1 lock in share mode，执行时扫描行数也是 1 行，执行时间是 0.2 毫秒。
image.png
看上去是不是更奇怪了？按理说 lock in share mode 还要加锁，时间应该更长才对啊。
可能有的同学已经有答案了。如果你还没有答案的话，我再给你一个提示信息，图 14 是这两个语句的执行输出结果。
image.png

第一个语句的查询结果里 c=1，带 lock in share mode 的语句返回的是 c=1000001。以下是复现步骤
image.png
你看到了，session A 先用 start transaction with consistent snapshot 命令启动了一个事务，之后 session B 才开始执行 update 语句。
session B 执行完 100 万次 update 语句后，id=1 这一行处于什么状态呢？你可以从下图找到答案。
image.png
session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。
带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。【回滚日志过大引起的一致性读慢，当前读快】
注意，undo log 里记录的其实是“把 2 改成 1”，“把 3 改成 2”这样的操作逻辑，画成减 1 的目的是方便你看图。

[!important]
关于undolog的基础，强烈建议阅读：http://mysql.taobao.org/monthly/2015/04/01/

问题

1
2
3
begin;
select * from t where c=5 for update;
commit;

这个语句序列是怎么加锁的呢？加的锁又是什么时候释放呢？
在RC隔离级别下，innodb先锁全表的所有行，返回server层，判断c是否等于5，然后释放c！=5的行锁。

关于回答，推荐看以下两个博客的分析
聊聊Select for update到底加了什么锁
select for update行锁or表锁，20个场景分析，还真得看情况
对于其他基础，分享一个博客：锁的常见分类
比较好理解的是，这个语句会命中 d=5 的这一行，对应的主键 id=5，因此在 select 语句执行完成后，id=5 这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。
由于字段 d 上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的 5 行记录上，会不会被加锁呢？

自己的一个尝试回答（可能错误）：

  • 在RC隔离级别下，如果条件是唯一索引，那么select…for update加的应该是行锁。一共加了三把锁，分别是 IX意向排他锁（表级别的锁，不影响插入）、两把X排他锁（行锁，分别对应唯一索引，主键索引）
  • 在RC隔离级别下，如果条件是主键，那么select…for update锁的也是行。查询条件是id的话，select…for update会加两把锁，分表是IX意向排他锁（表锁，不影响插入）、一把X排他锁（行锁，对于主键索引）
  • 在RC隔离级别下，如果条件是普通索引，且命中，加三把锁，IX意向排他锁（表锁）、两把X排他锁（行锁，分别对应普通索引的X锁，对应主键的X锁）。
  • 如果没有命中数据，则只加一把锁，即IX意向排他锁（表锁，不影响插入）
  • 在RC隔离级别下，如果条件是无索引，一共加了两把锁，分别是：IX意向排他锁（表锁）、一把X排他锁（行锁，对应主键的X锁）。
    • 备注：MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则
  • 在RR隔离级别下，如果条件是唯一索引，同RC隔离级别
  • 在RR隔离级别下，如果条件是主键，同RC隔离级别
  • 在RR隔离级别下，如果select…for update的查询条件是普通索引的话，命中查询记录的话，除了会加X锁（行锁），IX锁（表锁，不影响插入），还会加Gap 锁（间隙锁，会影响插入）。
  • 在RR隔离级别下，如果条件是无索引，1：IX锁（表级别，意向排他锁），2：每一行的数据记录：都加了X排他锁（行锁，锁的对象对应于主键Id）；3：锁为X锁，lock_data值为supremum pseudo-record，它表示：全表行锁，要走聚簇索引进行全部扫描。
    • 什么是supremum pseudo-record：如果查询条件有索引的话，类似于一个（索引最大值，+无穷）的虚拟间隙锁【通俗点讲，其实就是锁表了】

其他问题

[!question]
老师，我们公司今年打算迁数据库，也就是oracle迁到mysql，目前要决定选用哪个版本和分支，不知道要怎么分析，您有什么好的建议吗？

目前5.7是比较主流的，就用最新GA版本吧，记得做全量回归测试

[!question]
老师，最近项目mysql遇到一个难题， 表数据每天230万，一条语句的查询条件有1—40个，最坏情况下40，请问老师有没有好的建议，非常感谢

我觉得应对这种大数据量的多条件的查询的话换种思路，将mysql复杂的组合查询条件导入到es中作为key，主键id作为value，复杂的查询经过es后得到主键id，之后走mysql会好很多，目前公司是这样做的

20 | 幻读是什么，幻读有什么问题？

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
 
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

我们知道，InnoDB 的默认事务隔离级别是可重复读，所以本文接下来没有特殊说明的部分，都是设定在可重复读隔离级别下。

幻读是什么?

上一期的语句序列：

1
2
3
begin;
select * from t where d=5 for update;
commit;

现在，我们就来分析一下，如果只在 id=5 这一行加锁【假设，实际不是这样】，而其他行的不加锁的话，会怎么样。
下面先来看一下这个场景（注意：这是我假设的一个场景）：(假设只在id=5这一行加锁)
image.png
可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是 select * from t where d=5 for update。这个语句的意思你应该很清楚了，查所有 d=5 的行，而且使用的是当前读，并且加上写锁。现在，我们来看一下这三条 SQL 语句，分别会返回什么结果。

  1. Q1 只返回 id=5 这一行；
  2. 在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
  3. 在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。

其中，Q3 读到 id=1 这一行的现象，被称为“幻读”。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
这里，我需要对“幻读”做一个说明：

  1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
  2. 上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

不可重复读和幻读的表象都是，在同一个事务中，前后两次读取到的数据不一致，不同的是不可重复读是由于 delete 和 update 操作导致的数据不一致，而幻读是由于 insert 操作导致的数据不一致

如果只从[[基础篇 1-8节#08 事务到底是隔离的还是不隔离的？]]我们学到的事务可见性规则来分析的话，上面这三条 SQL 语句的返回结果都没有问题。
因为这三个查询都是加了 for update，都是当前读。而当前读的规则，就是要能读到所有已经提交的记录的最新值。并且，session B 和 sessionC 的两条语句，执行后就会提交，所以 Q2 和 Q3 就是应该看到这两个事务的操作效果，而且也看到了，这跟事务的可见性规则并不矛盾。
但是，这是不是真的没问题呢？
不，这里还真就有问题。

幻读有什么问题？

首先是语义上的。 session A 在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。
如果现在这样看感觉还不明显的话，我再往 session B 和 session C 里面分别加一条 SQL 语句，你再看看会出现什么现象。
image.png
session B 的第二条语句 update t set c=5 where id=0，语义是“我把 id=0、d=5 这一行的 c 值，改成了 5”。
由于在 T1 时刻，session A 还只是给 id=5 这一行加了行锁， 并没有给 id=0 这行加上锁。因此，session B 在 T2 时刻，是可以执行这两条 update 语句的。这样，就破坏了 session A 里 Q1 语句要锁住所有 d=5 的行的加锁声明。
session C 也是一样的道理，对 id=1 这一行的修改，也是破坏了 Q1 的加锁声明。
其次，是数据一致性的问题。
我们知道，锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。
为了说明这个问题，我给 session A 在 T1 时刻再加一个更新语句，即：update t set d=100 where d=5。
image.png
update 的加锁语义和 select …for update 是一致的，所以这时候加上这条 update 语句也很合理。session A 声明说“要给 d=5 的语句加上锁”，就是为了要更新数据，新加的这条 update 语句就是把它认为加上了锁的这一行的 d 值修改成了 100。
现在，我们来分析一下图 3 执行完成后，数据库里会是什么结果。

  1. 经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的 ;
  2. 经过 T2 时刻，id=0 这一行变成 (0,5,5);
  3. 经过 T4 时刻，表里面多了一行 (1,5,5);
  4. 其他行跟这个执行序列无关，保持不变。

这样看，这些数据也没啥问题，但是我们再来看看这时候 binlog 里面的内容

  1. T2 时刻，session B 事务提交，写入了两条语句；
  2. T4 时刻，session C 事务提交，写入了两条语句；
  3. T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。

我统一放到一起的话，就是这样的：

1
2
3
4
5
6
7
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
 
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
 
update t set d=100 where d=5;/* 所有 d=5 的行，d 改成 100*/

好，你应该看出问题了。这个语句序列，不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。
也就是说，id=0 和 id=1 这两行，发生了数据不一致。这个问题很严重，是不行的。
到这里，我们再回顾一下，这个数据不一致到底是怎么引入的？
我们分析一下可以知道，这是我们假设“select * from t where d=5 for update 这条语句只给 d=5 这一行，也就是 id=5 的这一行加锁”导致的。
所以我们认为，上面的设定不合理，要改。
那怎么改呢？我们把扫描过程中碰到的行，也都加上写锁，再来看看执行效果。
image.png
由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。
这样对于 id=0 这一行，在数据库里的最终结果还是 (0,5,5)。在 binlog 里面，执行序列是这样的：

1
2
3
4
5
6
7
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
 
update t set d=100 where d=5;/* 所有 d=5 的行，d 改成 100*/
 
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

可以看到，按照日志顺序执行，id=0 这一行的最终结果也是 (0,5,5)。所以，id=0 这一行的问题解决了。
但同时你也可以看到，id=1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。为什么我们已经这么“凶残”地，把所有的记录都上了锁，还是阻止不了 id=1 这一行的插入和更新呢？
原因很简单。在 T3 时刻，我们给所有行加锁的时候，id=1 这一行还不存在，不存在也就加不上锁。
也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录， 这也是为什么“幻读”会被单独拿出来解决的原因。
到这里，其实我们刚说明完文章的标题 ：幻读的定义和幻读有什么问题。
接下来，我们再看看 InnoDB 怎么解决幻读的问题。

如何解决幻读？

现在你知道了，产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。
顾名思义，间隙锁，锁的就是两个值之间的空隙。比如文章开头的表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。
image.png
这样，当你执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。
也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。
现在你知道了，数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。
比如行锁，分成读锁和写锁。下图就是这两种类型行锁的冲突关系。
image.png
也就是说，跟行锁有冲突关系的是“另外一个行锁”。

但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。 间隙锁之间都不存在冲突关系。
这句话不太好理解，我给你举个例子：
image.png
这里 session B 并不会被堵住。因为表 t 里并没有 c=7 这个记录，因此 session A 加的是间隙锁 (5,10)。而 session B 也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。
间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

备注：这篇文章中，如果没有特别说明，我们把间隙锁记为开区间，把 next-key lock 记为前开后闭区间。

你可能会问说，这个 supremum 从哪儿来的呢？
这是因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的“都是前开后闭区间”。
间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。
在前面的文章中，就有同学提到了这个问题。我把他的问题转述一下，对应到我们这个例子的表来说，业务逻辑这样的：任意锁住一行，如果这一行不存在的话就插入，如果存在这一行就更新它的数据，代码如下：

1
2
3
4
5
6
7
8
9
begin;
select * from t where id=N for update;
 
/* 如果行不存在 */
insert into t values(N,N,N);
/* 如果行存在 */
update t set d=N set id=N;
 
commit;

可能你会说，这个不是 insert … on duplicate key update 就能解决吗？但其实在有多个唯一键的时候，这个方法是不能满足这位提问同学的需求的。至于为什么，我会在后面的文章中再展开说明。
可能你会说，这个不是 insert … on duplicate key update 就能解决吗？但其实在有多个唯一键的时候，这个方法是不能满足这位提问同学的需求的。至于为什么，我会在后面的文章中再展开说明。
现在，我们就只讨论这个逻辑。
这个同学碰到的现象是，这个逻辑一旦有并发，就会碰到死锁。你一定也觉得奇怪，这个逻辑每次操作前用 for update 锁起来，已经是最严格的模式了，怎么还会有死锁呢？
这里，我用两个 session 来模拟并发，并假设 N=9。
image.png
你看到了，其实都不需要用到后面的 update 语句，就已经形成死锁了。我们按语句执行顺序来分析一下：

  1. session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
  2. session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
  3. session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
  4. session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

至此，两个 session 进入互相等待状态，形成死锁。当然，InnoDB 的死锁检测马上就发现了这对死锁关系，让 session A 的 insert 语句报错返回了。
你现在知道了，间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。其实，这还只是一个简单的例子，在下一篇文章中我们还会碰到更多、更复杂的例子。
你可能会说，为了解决幻读的问题，我们引入了这么一大串内容，有没有更简单一点的处理方法呢。
我在文章一开始就说过，如果没有特别说明，今天和你分析的问题都是在可重复读隔离级别下的，间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。
【这段深有体会，确实有一些系统的死锁会相对比较多，在将隔离级别调为RC之后改善了很多】

补充阅读：MySQL 默认隔离级别是RR，为什么阿里等大厂会改成RC？
在数据主从同步时，不同格式的 binlog 也对事务隔离级别有要求。
MySQL的binlog主要支持三种格式，分别是statement、row以及mixed，但是，RC 隔离级别只支持row格式的binlog。如果指定了mixed作为 binlog 格式，那么如果使用RC，服务器会自动使用基于row 格式的日志记录。
而 RR 的隔离级别同时支持statement、row以及mixed三种。

前面文章的评论区有同学留言说，他们公司就使用的是读提交隔离级别加 binlog_format=row 的组合。他曾问他们公司的 DBA 说，你为什么要这么配置。DBA 直接答复说，因为大家都这么用呀。
所以，这个同学在评论区就问说，这个配置到底合不合理。
关于这个问题本身的答案是，如果读提交隔离级别够用，也就是说，业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

但其实我想说的是，配置是否合理，跟业务场景有关，需要具体问题具体分析。

核心问题：什么业务场景，需要可重复读来保证？
主要是一些金融类业务，例如订单处理、账户余额查询、统计报表生成、业务状态查询

但是，如果 DBA 认为之所以这么用的原因是“大家都这么用”，那就有问题了，或者说，迟早会出问题。
比如说，大家都用读提交，可是逻辑备份的时候，mysqldump 为什么要把备份线程设置成可重复读呢？（这个我在前面的文章中已经解释过了，你可以再回顾下[[基础篇 1-8节#06 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？]]的内容）
然后，在备份期间，备份线程用的是可重复读，而业务线程用的是读提交。同时存在两种事务隔离级别，会不会有问题？
进一步地，这两个不同的隔离级别现象有什么不一样的，关于我们的业务，“用读提交就够了”这个结论是怎么得到的？
如果业务开发和运维团队这些问题都没有弄清楚，那么“没问题”这个结论，本身就是有问题的。

对于非索引字段进行update或select .. for update操作，代价极高。所有记录上锁，以及所有间隔的锁。
对于索引字段进行上述操作，代价一般。只有索引字段本身和附近的间隔会被加锁。
所以update、delete语句用不上索引是很恐怖的

其他问题

[!question]
如何看锁信息 ？

设置参数：set global innodb_status_output_locks=1;
然后使用show engine innodb status\G 查看，TRANSACTIONS 相关的信息下，就能看到锁信息。

[!question]
我看一些文章都说幻读是rr级别下的 ， rc 是不可重复读 。请问是我理解有误还是文章写的不准确

其实读提交隔离级别下看到的，严格来说不算。
因为这个就是读提交隔离级别下“设计内”的问题😄
这种感觉就是，对于读提交隔离级别，这个算“feature”,（是不是很熟悉）
对于可重复读，这个是”bug”, 所以要解决，称呼这个bug为幻读😄

21 | 为什么我只改一行的语句，锁这么多？

MySQL 后面的版本可能会改变加锁策略，所以本节规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。
因为间隙锁在可重复读隔离级别下才有效，所以本篇文章接下来的描述，若没有特殊说明，默认是可重复读隔离级别。
我总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

  1. 原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
  2. 原则 2：查找过程中访问到的对象才会加锁。
  3. 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。【注意这一行如果不存在的话还是间隙锁】
  4. 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。【order by desc可能不会退化为间隙锁】
  5. 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

表t的建表语句和初始化语句如下

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;
 
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：等值查询间隙锁

image.png
由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：

  1. 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
  2. 同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

案例二：非唯一索引等值锁

第二个例子是关于覆盖索引上的锁：
image.png

  1. 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5] 加上 next-key lock。
  2. 要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10] 加 next-key lock。【应该这样描述更加容易理解，因为c不是唯一索引，所以需要向下遍历到第一个不符合条件的值才能停止】
  3. 但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
  4. 根据原则 2 ，只有访问到的对象才会加锁 ，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。【如果是覆盖索引就不会给主键加锁，否则就会给主键加锁。】

但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。
需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。 执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。
这个例子说明，锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode。你可以自己验证一下效果。

案例三：主键索引范围锁

第三个例子是关于范围查询的。
举例之前，你可以先思考一下这个问题：对于我们这个表 t，下面这两条查询语句，加锁范围相同吗？

1
2
mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

你可能会想，id 定义为 int 类型，这两个语句就是等价的吧？其实，它们并不完全等价。
在逻辑上，这两条查语句肯定是等价的，但是它们的加锁规则不太一样。现在，我们就让 session A 执行第二个查询语句，来看看加锁效果。
image.png
现在我们就用前面提到的加锁规则，来分析一下 session A 会加什么锁呢？

  1. 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。 根据优化 1， 主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
  2. 范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。这样，session B 和 session C 的结果你就能理解了。
这里你需要注意一点，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

案例四：非唯一索引范围锁

接下来，我们再看两个范围查询加锁的例子，你可以对照着案例三来看。
需要注意的是，与案例三不同的是，案例四中查询语句的 where 部分用的是字段 c。
image.png
这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。
所以从结果上来看，sesson B 要插入（8,8,8) 的这个 insert 语句时就被堵住了。
这里需要扫描到 c=15 才停止扫描，是合理的，因为 InnoDB 要扫到 c=15，才知道不需要继续往后找了。

案例五：唯一索引范围锁 bug

image.png
session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。【InnoDB（5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。）的“bug”】
所以你看到了，session B 要更新 id=20 这一行，是会被锁住的。同样地，session C 要插入 id=16 的一行，也会被锁住。
照理说，这里锁住 id=20 这一行的行为，其实是没有必要的。因为扫描到 id=15，就可以确定不用往后再找了。但实现上还是这么做了，因此我认为这是个 bug。
我也曾找社区的专家讨论过，官方 bug 系统上也有提到，但是并未被 verified。所以，认为这是 bug 这个事儿，也只能算我的一家之言，如果你有其他见解的话，也欢迎你提出来。

案例六：非唯一索引上存在”等值”的例子

接下来的例子，是为了更好地说明“间隙”这个概念。这里，我给表 t 插入一条新记录。

1
mysql> insert into t values(30,10,30);

新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。那么，这时候索引 c 上的间隙是什么状态了呢？你要知道，由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。
image.png
可以看到，虽然有两个 c=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个 c=10 的记录之间，也是有间隙的。
图中我画出了索引 c 上的主键 id。为了跟间隙锁的开区间形式进行区别，我用 (c=10,id=30) 这样的形式，来表示索引上的一行。
现在，我们来看一下案例六。
这次我们用 delete 语句来验证。注意，delete 语句加锁的逻辑，其实跟 select … for update 是类似的，也就是我在文章开始总结的两个“原则”、两个“优化”和一个“bug”。
image.png
这时，session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。
也就是说，这个 delete 语句在索引 c 上的加锁范围，就是下图中蓝色区域覆盖的部分。
image.png
这个蓝色区域左右两边都是虚线，表示开区间，即 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。

举个例子：如果session b插入(4,5,50)，不会被锁，如果插入（6,5,50） 会被锁住，因为6,5,50根据主键来排的话 是在5,5,10后面的【二级索引叶子节点存储的是主键值，而二级索引的叶子节点是有序的，这样(6,5,5)插入的时候就会排序到(5,5,5)之后，进入了间隙锁范围内——>即二级索引的间隙锁】

案例七：limit 语句加锁

例子 6 也有一个对照案例，场景如下所示：
image.png
这个例子里，session A 的 delete 语句加了 limit 2。你知道表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。可以看到，session B 的 insert 语句执行通过了，跟案例六的结果不同。
这是因为，案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。
因此，索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：
image.png
可以看到，(c=10,id=30）之后的这个间隙并没有在加锁范围里，因此 insert 语句插入 c=12 是可以执行成功的。
这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

案例八：一个死锁的例子

前面的例子中，我们在分析的时候，是按照 next-key lock 的逻辑来分析的，因为这样分析比较方便。最后我们再看一个案例，目的是说明：next-key lock 实际上是间隙锁和行锁加起来的结果。

你一定会疑惑，这个概念不是一开始就说了吗？不要着急，我们先来看下面这个例子：
image.png
现在，我们按时间顺序来分析一下为什么是这样的结果。

  1. session A 启动事务后执行查询语句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和间隙锁 (10,15)；
  2. session B 的 update 语句也要在索引 c 上加 next-key lock(5,10] ，进入锁等待；
  3. 然后 session A 要再插入 (8,8,8) 这一行，被 session B 的间隙锁锁住。由于出现了死锁，InnoDB 让 session B 回滚。

你可能会问，session B 的 next-key lock 不是还没申请成功吗？
其实是这样的，session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。【前面提到过，间隙锁和间隙锁之间并不冲突，间隙锁和insert到这个间隙的语句才会冲突】
也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。
也就是说，读提交隔离级别下，锁的范围更小，锁的时间更短，这也是不少业务都默认使用读提交隔离级别的原因。

小结

这里我再次说明一下，我们上面的所有案例都是在可重复读隔离级别 (repeatable-read) 下验证的。同时，可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的。
在最后的案例中，你可以清楚地知道 next-key lock 实际上是由间隙锁加行锁实现的。如果切换到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁的部分。
其实读提交隔离级别在外键场景下还是有间隙锁，相对比较复杂，我们今天先不展开。
另外，在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。
也就是说，读提交隔离级别下，锁的范围更小，锁的时间更短，这也是不少业务都默认使用读提交隔离级别的原因。
不过，我希望你学过今天的课程以后，可以对 next-key lock 的概念有更清晰的认识，并且会用加锁规则去判断语句的加锁范围。
在业务需要使用可重复读隔离级别的时候，能够更细致地设计操作数据库的语句，解决幻读问题的同时，最大限度地提升系统并行处理事务的能力。

[!abstract]
本节的内容可以总结为如下：
1、查询过程中访问到的对象才会加锁，而加锁的基本单位是next-key lock（前开后闭）；
2、等值查询上MySQL的优化：索引上的等值查询，如果是唯一索引，next-key lock会退化为行锁，如果不是唯一索引，需要访问到第一个不满足条件的值，此时next-key lock会退化为间隙锁；
3、范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止；

其他问题

[!info]
有一个小伙伴通过画图的案例，以MySQL 中关于gap lock / next-key lock 的一个问题来复习本质的内容，可以参考下
https://blog.hellowood.dev/posts/mysql-中关于gap-lock-next-key-lock-的一个问题/
注意：答主的分析是有误的，真正的解释如下

  1. INSERT INTO z VALUES (2, 4);/*success*/ id=2 b=4, 这时4可以插在第二行（3，4）数据之前或者之后，但因为id=2,只能插在第二行，因为也会按照主键id排序，这时可以插入成功
  2. INSERT INTO z VALUES (2, 8);/*blocked*/ id=2 b=8, 8可以插在第四行（7，8）前或者后，但是因为2比第四行的id=7小，所以只会插在前面，但这里b已经被锁住了，只能blocked了
  3. INSERT INTO z VALUES (4, 4);/*blocked*/ 和以上分析一致，只能插在第二行后面，可是这个范围b上已加锁，也只能被block住
    原因：innodb的二级索引隐含了主键id，非唯一索引索引值相同的情况是按照主键id排序
    Update/delete语句，通过辅助索引加锁的语句，考虑额外地主键索引上也要加锁。–可以看看姜承尧《MYSQL内核》第九章

[!question]
Gap是一个动态的概念，以下是例子
老师，你好： 我练习实例的时候发现一个问题：如 案例五：唯一索引范围锁 bug
begin;
select * from t where id>10 and id<=15 for update;
1、执行如上语句加锁范围(10,15]和(15,20]；
2、因为10未加锁，所以我单独再开一个连接，执行delete from t where id=10;不会锁等待，能正常删除；
3、但是我再执行insert into t values(10,10,10); 语句会等待，无法正常执行；
4、经过分析我发现第一个连接执行的语句的加锁范围已经变成(5,15]和(15,20]，代表锁蔓延了；这是什么原因呢？

delete id=10之后，为了原先的(10,15]中的gap锁，会用10的上一个值5来代替。
即更新边界值导致间隙变大，再改成原值改不回去，被阻塞

[!faq]
RC隔离界别下的“semi-consistent” read优化
版本：mysql 5.6.39
CREATE TABLE t (
a int(11) NOT NULL,
b int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into t values(1,1),(2,2),(3,3),(4,4),(5,5);
采用READ-COMMITTED隔离级别
案例1、
session A：
begin;
update t set a=6 where b=1;
session B：
begin;
update t set a=7 where b=2;
A和B均能执行成功
问题1：官档上说对于RC且全表扫描的update，先逐行添加行锁然后释放掉不符合where条件的，那么session A成功对(1,1)加锁，理论上session B在扫描(1,1)并尝试加锁时会被阻塞，为何还能执行成功？官档链接：https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
案例2：
session A：
begin;
update t set a=6 where b=1;
session B：
begin;
delete from t where b=2; – 被阻塞
问题2：为何案例1 中的session B不会被阻塞，而案例2的却被session A的行数阻塞，update和delete都是全部扫描，难道加锁机制不一样？

好问题，在read-commited隔离级别下，update语句
有一个“semi-consistent” read优化，
意思是，如果update语句碰到一个已经被锁了的行，会读入最新的版本，然后判断一下是不是满足查询条件，
a)如果不满足，就直接跳过；
b) 如果满足，才进入锁等待
你的第二个问题：这个策略，只对update有效，delete无效

问题

下面这个图的执行序列中，为什么 session B 的 insert 语句会被堵住。
image.png
我们用上一篇的加锁规则来分析一下，看看 session A 的 select 语句加了哪些锁：

  1. 由于是 order by c desc，第一个要定位的是索引 c 上“最右边的”c=20 的行，所以会加上间隙锁 (20,25) 和 next-key lock (15,20]。【索引反着找不会退化为间隙锁，C=10还是要锁的，如果不锁可能被删除】（这点很容易迷惑）
  2. 在索引 c 上向左遍历，要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]，这正是阻塞 session B 的 insert 语句的原因。【加锁的基本单位是next-key-lock ，扫描过程中扫到了c=10 ，加锁就就是加的(5，10]】
  3. 在扫描过程中，c=20、c=15、c=10 这三行都存在值，由于是 select *，所以会在主键 id 上加三个行锁。

因此，session A 的 select 语句锁的范围就是：【备注：即两个索引上都会有锁】

  1. 索引 c 上 (5, 25)；
  2. 主键索引上 id=15、20 两个行锁。

这里，我再啰嗦下，你会发现我在文章中，每次加锁都会说明是加在“哪个索引上”的。因为，锁就是加在索引上的，这是 InnoDB 的一个基础设定，需要你在分析问题的时候要一直记得。

22 | MySQL有哪些“饮鸩止渴”提高性能的方法？

短连接风暴

正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。
[[基础篇 1-8节]]中说过MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。
在数据库压力比较小的时候，这些额外的成本并不明显。
但是，短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。
在机器负载比较高的时候，处理现有请求的时间变长，每个连接保持的时间也更长。这时，再有新建连接的话，就可能会超过 max_connections 的限制。
碰到这种情况时，一个比较自然的想法，就是调高 max_connections 的值。但这样做是有风险的。因为设计 max_connections 这个参数的目的是想保护 MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。
那么这种情况下，你还有没有别的建议呢？我这里还有两种方法，但要注意，这些方法都是有损的。
第一种方法：先处理掉那些占着连接但是不工作的线程。
max_connections 的计算，不是看谁在 running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过 kill connection 主动踢掉。这个行为跟事先设置 wait_timeout 的效果是一样的。设置 wait_timeout 参数表示的是，一个线程空闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。【show variables like ‘wait_timeout’; 本机是28880】
但是需要注意，在 show processlist 的结果里，踢掉显示为 sleep 的线程，可能是有损的。我们来看下面这个例子。
image.png
在上面这个例子里，如果断开 session A 的连接，因为这时候 session A 还没有提交，所以 MySQL 只能按照回滚事务来处理；而断开 session B 的连接，就没什么大影响。所以，如果按照优先级来说，你应该优先断开像 session B 这样的事务外空闲的连接。
但是，怎么判断哪些是事务外空闲的呢？session C 在 T 时刻之后的 30 秒执行 show processlist，看到的结果是这样的。
image.png
图中 id=4 和 id=5 的两个会话都是 Sleep 状态。而要看事务具体状态的话，你可以查 information_schema 库的 innodb_trx 表。
image.png
这个结果里，trx_mysql_thread_id=4，表示 id=4 的线程还处在事务中。
因此，如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。
从服务端断开连接使用的是 kill connection + id 的命令， 一个客户端处于 sleep 状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。
从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL 一直没恢复”。【在spring项目中使用不同的数据库连接池框架，可能会有不同的结果】
你可能觉得这是一个冷笑话，但实际上我碰到过不下 10 次。
所以，如果你是一个支持业务的 DBA，不要假设所有的应用代码都会被正确地处理。即使只是一个断开连接的操作，也要确保通知到业务开发团队。
第二种方法：减少连接过程的消耗。
有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。
跳过权限验证的方法是：重启数据库，并使用 –skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。【重置、找回密码使用的就是这个方式】
但是，这种方法特别符合我们标题里说的“饮鸩止渴”，风险极高，是我特别不建议使用的方案。尤其你的库外网可访问的话，就更不能这么做了。
在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 –skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。可见，MySQL 官方对 skip-grant-tables 这个参数的安全问题也很重视。
除了短连接数暴增可能会带来性能问题外，实际上，我们在线上碰到更多的是查询或者更新语句导致的性能问题。其中，查询问题比较典型的有两类，一类是由新出现的慢查询导致的，一类是由 QPS（每秒查询数）突增导致的。而关于更新语句导致的性能问题，我会在下一篇文章和你展开说明。

慢查询性能问题

在 MySQL 中，会引发性能问题的慢查询，大体有以下三种可能：

  1. 索引没有设计好；
  2. SQL 语句没写好；
  3. MySQL 选错了索引。

接下来，我们就具体分析一下这三种可能，以及对应的解决方案。
导致慢查询的第一种可能是，索引没有设计好。
这种场景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。
比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

  1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
  2. 执行主备切换；
  3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

这是一个“古老”的 DDL 方案。平时在做变更的时候，你应该考虑类似 gh-ost 这样的方案，更加稳妥。但是在需要紧急处理时，上面这个方案的效率是最高的。
导致慢查询的第二种可能是，语句没写好。
比如，我们犯了在[[#18 为什么这些SQL语句逻辑相同，性能却差异巨大？]]中提到的那些错误，导致语句没有使用上索引。
这时，我们可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式。
比如，语句被错误地写成了 select * from t where id + 1 = 10000，你可以通过下面的方式，增加一个语句改写规则。

1
mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select * from t where id + 1 = ?", "select * from t where id = ? - 1", "db1"); call query_rewrite.flush_rewrite_rules();

这里，call query_rewrite.flush_rewrite_rules() 这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。你可以用图 4 中的方法来确认改写规则是否生效。
image.png
导致慢查询的第三种可能，就是碰上了我们在第 10 篇文章《MySQL 为什么有时候会选错索引？》中提到的情况，MySQL 选错了索引。
这时候，应急方案就是给这个语句加上 force index。
同样地，使用查询重写功能，给原来的语句加上 force index，也可以解决这个问题。
上面我和你讨论的由慢查询导致性能问题的三种可能情况，实际上出现最多的是前两种，即：索引没设计好和语句没写好。而这两种情况，恰恰是完全可以避免的。比如，通过下面这个过程，我们就可以预先发现问题。

  1. 上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
  2. 在测试表里插入模拟线上的数据，做一遍回归测试；
  3. 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。

不要吝啬这段花在上线前的“额外”时间，因为这会帮你省下很多故障复盘的时间。
如果新增的 SQL 语句不多，手动跑一下就可以。而如果是新项目的话，或者是修改了原有项目的 表结构设计，全量回归测试都是必要的。这时候，你需要工具帮你检查所有的 SQL 语句的返回结果。比如，你可以使用开源工具 pt-query-digest(https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html)。

QPS 突增问题

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。
我之前碰到过一类情况，是由一个新功能的 bug 导致的。当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。
而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用。我这里再和你展开说明一下。

  1. 一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。

    MySQL 运维体系中的白名单机制一般用于限制数据库的访问，只允许特定的 IP 地址或者主机名访问数据库，以增加数据库的安全性。

  2. 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
  3. 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成”select 1” 返回。

当然，这个操作的风险很高，需要你特别细致。它可能存在两个副作用：

  1. 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；
  2. 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。

所以，方案 3 是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。
同时你会发现，其实方案 1 和 2 都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。由此可见，更多的准备，往往意味着更稳定的系统。

问题

你是否碰到过，在业务高峰期需要临时救火的场景？你又是怎么处理的呢？
@Long 同学，在留言中提到了几个很好的场景。

  • 其中第 3 个问题，“如果一个数据库是被客户端的压力打满导致无法响应的，重启数据库是没用的。”，说明他很好地思考了。 这个问题是因为重启之后，业务请求还会再发。而且由于是重启，buffer pool 被清空，可能会导致语句执行得更慢。
  • 他提到的第 4 个问题也很典型。有时候一个表上会出现多个单字段索引（而且往往这是因为运维工程师对索引原理不够清晰做的设计），这样就可能出现优化器选择索引合并算法的现象。但实际上，索引合并算法的效率并不好。而通过将其中的一个索引改成联合索引的方法，是一个很好的应对方案。

还有其他几个同学提到的问题场景，也很好，很值得你一看。

@Max 同学提到一个很好的例子：客户端程序的连接器，连接完成后会做一些诸如 show columns 的操作，在短连接模式下这个影响就非常大了。
这个提醒我们，在 review 项目的时候，不止要 review 我们自己业务的代码，也要 review 连接器的行为。一般做法就是在测试环境，把 general_log 打开，用业务行为触发连接，然后通过 general log 分析连接器的行为。

@Manjusaka 同学的留言中，第二点提得非常好：如果你的数据库请求模式直接对应于客户请求，这往往是一个危险的设计。因为客户行为不可控，可能突然因为你们公司的一个运营推广，压力暴增，这样很容易把数据库打挂。
在设计模型里面设计一层，专门负责管理请求和数据库服务资源，对于比较重要和大流量的业务，是一个好的设计方向。

@Vincent 同学提了一个好问题，用文中提到的 DDL 方案，会导致 binlog 里面少了这个 DDL 语句，后续影响备份恢复的功能。由于需要另一个知识点（主备同步协议），我放在后面的文章中说明。

[!tip]
关于kill threadId 跟 kill query threadId的小例子

客户端（mysqlworkbench）送给server端的是KILL QUERY thread_id，而不是Kill thread_id，
所以MySQL只是终止了事务中的statement执行，但是并不会释放锁，因为目前的琐的获取和释放都是基于事务结束的（提交或者回滚）。
这里面关于kill query/ thread_id的区别解释
https://dev.mysql.com/doc/refman/5.6/en/kill.html
解决方法：
自己解决：kill 对应的thread_id，或者关闭执行窗口（这个时候会送个quit给server端）。
别人解决：有super权限的人kill thread_id。